package com.example.demo.util;

import java.sql.SQLException;

import javax.sql.DataSource;

import lombok.extern.slf4j.Slf4j;

/**
 * 
 * @author xzw
 * sql工具类
 */
@Slf4j
public class SqlUtils {
	
	private static boolean isMysql = false;
	
	private static boolean isOracle = false;
	
	private final static String DELETE = "DELETE";
	
	private final static String UPDATE = "UPDATE";
	
	static {
		DataSource datasource = SpringContextUtil.getBean("masterDataSource", DataSource.class);
		
		String dbType = null;
		try {
			dbType = datasource.getConnection().getMetaData().getDatabaseProductName().toUpperCase();
		} catch (SQLException e) {
			log.error("获取数据库类型失败",e);
		}
		
		log.info("使用的数据库类型为:{}",dbType);
		
		if("MYSQL".equals(dbType)) {
			isMysql = true;
		} else if("ORACLE".equals(dbType)) {
			isOracle = true;
		}
		
	}

	public static String getInsertHisSql (String oldSql) throws Exception{
		
		String[] sqlArr = oldSql.split(" ");
		
		String exeType = sqlArr[0].toUpperCase();
		
		if("SELECT".equals(exeType) || "INSERT".equals(exeType)) {
			return null;
		}
		
		String tableName = null;
		
		String opType = "";
		
		if(UPDATE.equals(exeType)) {
			tableName = sqlArr[1];
			opType = "U";
		} else if (DELETE.equals(exeType)) {
			tableName = sqlArr[2];
			opType = "D";
		} else {
			return null;
		}
		
		String whereSql = null;
		
		if(oldSql.toUpperCase().indexOf("WHERE")>-1) {
			whereSql = oldSql.substring(oldSql.toUpperCase().indexOf("WHERE"));
		} else {
			return null;
		}
		
		StringBuilder hisSql = new StringBuilder();
		
		String hisTableName = tableName + "_his";
		
		hisSql.append("INSERT INTO ").append(hisTableName).append(" SELECT ");
		
		if(isMysql) {
			hisSql.append(" *,UUID(),NOW(),'").append(opType).append("' FROM ").append(tableName).append(" ").append(whereSql);
		} else if (isOracle) {
			hisSql.append(" x.*,sys_guid() as hisid,sysdate() as histime,").append(opType).append(" as optype FROM ")
				.append(tableName).append(" a ").append(whereSql);
		} else {
			hisSql.append(" *,  convert(varchar(38), newid()), getdate(),'").append(opType).append("' FROM ")
				.append(tableName).append(" ").append(whereSql);
		}
		
		log.debug("记录历史数据的sql>>>{}",hisSql);
		
		return hisSql.toString();
	}
}
